/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bulkmailer.email.msg.manager;

import bulkmailer.beans.Email;

import bulkmailer.db.DBConnector;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import org.apache.log4j.Logger;

/**
 *
 * @author ravi
 */
public class EmailMsgManager {

    Connection con = null;

    public void setMailMsg(Email email) {
        this.getConnection();
        try {
            String query = "INSERT INTO mail_store(m_subject,m_body,m_sign, m_desc, m_disclaimer) values(?,?,?,?,?)";
            PreparedStatement stmt = null;
            ResultSet resultSet = null;
            stmt = con.prepareStatement(query);
            stmt.setString(1, email.getSubject());
            stmt.setString(2, email.getBody());
            stmt.setString(3, email.getSign());
            stmt.setString(4, email.getDescription());
            stmt.setString(5, email.getDisclaimer());
            stmt.execute();

        } catch (SQLException ex) {
            System.out.println("Exception" + ex.getMessage());

        }
    }

    public Email getLatestMail() {
        Email email = null;
        this.getConnection();
        try {
            String query = "SELECT * FROM mail_store WHERE status = 'LISTED' ORDER BY mail_uid DESC LIMIT 1";
            PreparedStatement stmt = null;
            ResultSet resultSet = null;
            stmt = con.prepareStatement(query);
            resultSet = stmt.executeQuery();
            if (resultSet != null) {
                while (resultSet.next()) {
                    email = new Email();
                    email.setMailUid(resultSet.getInt("mail_uid"));
                    email.setSubject(resultSet.getString("m_subject"));
                    email.setBody(resultSet.getString("m_body"));
                    email.setSign(resultSet.getString("m_sign"));
                    email.setDescription(resultSet.getString("m_desc"));
                    email.setDisclaimer(resultSet.getString("m_disclaimer"));
                }
            }

        } catch (SQLException ex) {
            System.out.println("Exception" + ex.getMessage());

        }
        return email;
    }

    public void deleteEmailMsg(int mail_uid) {
        this.getConnection();
        try {
            String query = "UPDATE mail_store set status = 'DELETED' where mail_uid = ?";
            PreparedStatement stmt = null;
            stmt = con.prepareStatement(query);
            stmt.setInt(1, mail_uid);
            stmt.execute();

        } catch (SQLException ex) {
            System.out.println("Exception" + ex.getMessage());

        }
    }

    private void getConnection() {
        try {
            con = DBConnector.getConnection(DBConnector.SCHEMA_NAME);
        } catch (Exception ex) {
            Logger.getRootLogger().error(ex.toString());
        }
        if (con == null) {
            Logger.getRootLogger().error("database connection failed, can't fetch domains");

        } else {
            Logger.getRootLogger().debug("Database connection successful");
        }
    }

    public LinkedList<Email> getAllMessages() {
        LinkedList<Email> emailList = null;
        Email email = null;
        this.getConnection();
        try {
            String query = "SELECT * FROM mail_store WHERE status = 'LISTED' ORDER BY mail_uid DESC";
            PreparedStatement stmt = null;
            ResultSet resultSet = null;
            stmt = con.prepareStatement(query);
            resultSet = stmt.executeQuery();
            if (resultSet != null) {
                emailList = new LinkedList<Email>();
                while (resultSet.next()) {
                    email = new Email();
                    email.setMailUid(resultSet.getInt("mail_uid"));
                    email.setSubject(resultSet.getString("m_subject"));
                    email.setBody(resultSet.getString("m_body"));
                    email.setSign(resultSet.getString("m_sign"));
                    email.setDescription(resultSet.getString("m_desc"));
                    email.setDisclaimer(resultSet.getString("m_disclaimer"));
                    emailList.add(email);
                }
            }

        } catch (SQLException ex) {
            System.out.println("Exception" + ex.getMessage());
        }
        return emailList;
    }

    public Email getMailWithIndex(int mailIndex) {
        Email email = null;
        this.getConnection();
        try {
            String query = "SELECT * FROM mail_store WHERE mail_uid = ? AND status = 'LISTED' ORDER BY mail_uid DESC LIMIT 1";
            PreparedStatement stmt = null;
            ResultSet resultSet = null;
            stmt = con.prepareStatement(query);
            stmt.setInt(1, mailIndex);
            resultSet = stmt.executeQuery();
            if (resultSet != null) {
                while (resultSet.next()) {
                    email = new Email();
                    email.setMailUid(resultSet.getInt("mail_uid"));
                    email.setSubject(resultSet.getString("m_subject"));
                    email.setBody(resultSet.getString("m_body"));
                    email.setSign(resultSet.getString("m_sign"));
                    email.setDescription(resultSet.getString("m_desc"));
                    email.setDisclaimer(resultSet.getString("m_disclaimer"));
                }
            }

        } catch (SQLException ex) {
            System.out.println("Exception" + ex.getMessage());

        }
        return email;
    }
}
